﻿// --------------------------------------------------------------------------------------------------------------------
// <copyright file="DatabaseExtension.cs" company="emrecoskun.net">
//   ecblog.codeplex.com - emrecoskun.net - ibrahimatay.org
// </copyright>
// <summary>
//   The database extensions
// </summary>
// --------------------------------------------------------------------------------------------------------------------

namespace EcBlog.Extensions
{
    using System.Collections.Generic;
    using System.Data.SqlClient;
    using System.Data.SqlServerCe;
    using System.Linq;

    using EcBlog.Data.Sql;

    /// <summary>
    /// The database extensions
    /// </summary>
    public static class DatabaseExtension
    {
        /// <summary>
        /// Determines whether [is column exists] [the specified context].
        /// </summary>
        /// <param name="context">The context.</param>
        /// <param name="tableName">Name of the table.</param>
        /// <param name="columnName">Name of the column.</param>
        /// <returns>
        ///   <c>true</c> if [is column exists] [the specified context]; otherwise, <c>false</c>.
        /// </returns>
        public static bool IsColumnExists(this EcModelContext context, string tableName, string columnName)
        {
            var q =
                context.Database.SqlQuery<int>("SELECT count(1) FROM INFORMATION_SCHEMA.columns WHERE (column_Name = '" + columnName + "') AND (table_Name = '" + tableName + "')").ToList();
            return q.Count > 0 && q[0] > 0;
        }

        /// <summary>
        /// Determines whether [is index exists] [the specified context].
        /// </summary>
        /// <param name="context">The context.</param>
        /// <param name="tableName">Name of the table.</param>
        /// <param name="columnName">Name of the column.</param>
        /// <returns>
        ///   <c>true</c> if [is index exists] [the specified context]; otherwise, <c>false</c>.
        /// </returns>
        public static bool IsIndexExists(this EcModelContext context, string tableName, string columnName)
        {
            List<int> result = null;
            if (context.Database.Connection is SqlConnection)
            {
                // conneciton for SQL Server then
                result =
                context.Database.SqlQuery<int>("SELECT count(1) FROM INFORMATION_SCHEMA.COLUMNS WHERE (column_Name = '" + columnName + "') AND (table_Name = '" + tableName + "')").ToList();
            }
            else if (context.Database.Connection is SqlCeConnection)
            {
                // conneciton for SQLCe then
                result =
                    context.Database.SqlQuery<int>("SELECT count(1) FROM INFORMATION_SCHEMA.INDEXES WHERE (column_Name = '" + columnName + "') AND (table_Name = '" + tableName + "')").ToList();    
            }
            
            return result != null && (result.Count > 0 && result[0] > 0);
        }

        /// <summary>
        /// Determines whether [is table exists] [the specified context].
        /// </summary>
        /// <param name="context">The context.</param>
        /// <param name="tableName">Name of the table.</param>
        /// <returns>
        ///   <c>true</c> if [is table exists] [the specified context]; otherwise, <c>false</c>.
        /// </returns>
        public static bool IsTableExists(this EcModelContext context, string tableName)
        {
            var q =
                context.Database.SqlQuery<int>("SELECT COUNT(1) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '" +
                                               tableName + "'").ToList();
            return q.Count > 0 && q[0] > 0;
        }

        /// <summary>
        /// Determines whether [is foreign key exists] [the specified context].
        /// </summary>
        /// <param name="context">The context.</param>
        /// <param name="tableName">Name of the table.</param>
        /// <param name="columnName">Name of the column.</param>
        /// <returns>
        ///   <c>true</c> if [is foreign key exists] [the specified context]; otherwise, <c>false</c>.
        /// </returns>
        public static bool IsForeignKeyExists(this EcModelContext context, string tableName, string columnName)
        {
            var q =
                context.Database.SqlQuery<int>(
                    "SELECT COUNT(1) FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE A INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS B ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME WHERE B.CONSTRAINT_TYPE = 'FOREIGN KEY' AND A.TABLE_NAME = '" +
                    tableName + "' AND A.COLUMN_NAME = '" + columnName + "'").ToList();
            return q.Count > 0 && q[0] > 0;
        }

        /// <summary>
        /// Gets the name of the foreign key.
        /// </summary>
        /// <param name="context">The context.</param>
        /// <param name="tableName">Name of the table.</param>
        /// <param name="columnName">Name of the column.</param>
        /// <returns>The name of foreign key</returns>
        public static string GetForeignKeyName(this EcModelContext context, string tableName, string columnName)
        {
            var q =
                context.Database.SqlQuery<string>(
                    "SELECT A.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE A INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS B ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME WHERE B.CONSTRAINT_TYPE = 'FOREIGN KEY' AND A.TABLE_NAME = '" +
                    tableName + "' AND A.COLUMN_NAME = '" + columnName + "'").ToList();
            return q.Count > 0 ? q[0] : null;
        }

        /// <summary>
        /// Determines whether [is primary key exists] [the specified context].
        /// </summary>
        /// <param name="context">The context.</param>
        /// <param name="tableName">Name of the table.</param>
        /// <returns>
        ///   <c>true</c> if [is primary key exists] [the specified context]; otherwise, <c>false</c>.
        /// </returns>
        public static bool IsPrimaryKeyExists(this EcModelContext context, string tableName)
        {
            var q =
                context.Database.SqlQuery<int>(
                    "SELECT COUNT(1) FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE A INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS B ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME WHERE B.CONSTRAINT_TYPE = 'PRIMARY KEY' AND A.TABLE_NAME = '" +
                    tableName + "'").ToList();
            return q.Count > 0 && q[0] > 0;
        }

        /// <summary>
        /// Gets the name of the primary key.
        /// </summary>
        /// <param name="context">The context.</param>
        /// <param name="tableName">Name of the table.</param>
        /// <returns>The name of primary key</returns>
        public static string GetPrimaryKeyName(this EcModelContext context, string tableName)
        {
            var q =
                context.Database.SqlQuery<string>(
                    "SELECT A.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE A INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS B ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME WHERE B.CONSTRAINT_TYPE = 'PRIMARY KEY' AND A.TABLE_NAME = '" +
                    tableName + "'").ToList();
            return q.Count > 0 ? q[0] : null;
        }
    }
}